﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace OPMS_V1.DOL
{
    public class RegisterDOB
    {
        private const String connString = "server=localhost;database=OPMS;Integrated Security=SSPI";
        SqlConnection conn = new SqlConnection(connString);
        SqlDataReader reader = null;

        /// <summary>
        /// This function is used for registering the user to the database
        /// </summary>
        /// <param name="accountName"></param>
        /// <param name="email"></param>
        /// <param name="password"></param>
        /// <param name="accountStatus"></param>
        /// <param name="accountType"></param>
        /// <param name="IdentificationId"></param>
        /// <param name="lastLogin"></param>
        /// <returns></returns>
        #region Register User to the database
        public DataSet RegisterUser(String accountName, String email, String password, String accountStatus, String accountType, String IdentificationId, String lastLogin)
        {
            //Open the Connection
            using (SqlConnection conn = new SqlConnection(connString))
            {
                //The connection here is of the type Stored Procedure

                SqlCommand command = new SqlCommand("Register_User", conn);
                command.CommandType = CommandType.StoredProcedure;
                
                //Passing in the Parameters in to the Stored Procedures
                command.Parameters.Add("@loginID", SqlDbType.VarChar).Value = accountName;
                command.Parameters.Add("@password", SqlDbType.VarChar).Value = password;
                command.Parameters.Add("@accountType", SqlDbType.VarChar).Value = accountType;
                command.Parameters.Add("@lastLogin", SqlDbType.DateTime).Value = lastLogin;
                command.Parameters.Add("@identificationID", SqlDbType.VarChar).Value = IdentificationId;
                command.Parameters.Add("@accStatus", SqlDbType.VarChar).Value = accountStatus;
                command.Parameters.Add("@email", SqlDbType.VarChar).Value = email;

                //Fill in the dataset
                SqlDataAdapter da = new SqlDataAdapter(command);
                DataSet data = new DataSet();
                da.Fill(data);

                return data;
            }
        }
        #endregion Register User

        
        /// <summary>
        /// This function is used for returning all the usernames from the 
        /// database to check their existance.
        /// </summary>
        /// <returns></returns>
        #region getUsernames() To get all the usernames for their existance
        public List<String> getUsernames()
        {
            using (SqlCommand command = new SqlCommand("ListAllUserNames", conn))
            {
                List<String> allusernames = new List<string>();
                try
                {
                    //Open the SQL Connection
                    conn.Open();

                    //Run the command type as a stored procedure
                    command.CommandType = CommandType.StoredProcedure;

                    //Execute the reader for retrieving the results from the database
                    reader = command.ExecuteReader();

                    //If results forund the read the data
                    if (reader.HasRows)
                    {
                        reader.Read();
                        while (reader.Read())
                        {
                            string usernames = reader.GetString(0);
                            //Add the results to the List<...>
                            if (allusernames.Count > 0)
                            {
                                if (usernames != "Not Available") { allusernames.Add(usernames); }
                            }
                            else
                            {
                                allusernames.Add(usernames);
                            }
                        }
                    }
                    //Close the SqlDataReader
                    reader.Close();
                }
                catch (SqlException)
                { }
                finally{
                    //If the connection state is open then close the connection to the database.
                    if(conn.State==ConnectionState.Open)
                    {
                        conn.Close();
                    }
                }
                return allusernames;
            }
        }
        #endregion getUsernames()

        /// <summary>
        /// This function is used for returning all the usernames from the 
        /// database to check their existance.
        /// </summary>
        /// <returns></returns>
        #region getEmailAddress() To get all the usernames for their existance
        public List<String> getEmailAddress()
        {
            using (SqlCommand command = new SqlCommand("ListAllLoginEmailAddress", conn))
            {
                List<String> allemailaddress = new List<string>();
                try
                {
                    //Open the SQL Connection
                    conn.Open();

                    //Run the command type as a stored procedure
                    command.CommandType = CommandType.StoredProcedure;

                    //Execute the reader for retrieving the results from the database
                    reader = command.ExecuteReader();

                    //If results forund the read the data
                    if (reader.HasRows)
                    {
                        reader.Read();
                        while (reader.Read())
                        {
                            string email = reader.GetString(0);
                            //Add the results to the List<...>
                            if (allemailaddress.Count > 0)
                            {
                                if (email != "Not Available") { allemailaddress.Add(email); }
                            }
                            else
                            {
                                allemailaddress.Add(email);
                            }
                        }
                    }
                    //Close the SqlDataReader
                    reader.Close();
                }
                catch (SqlException)
                { }
                finally
                {
                    //If the connection state is open then close the connection to the database.
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                }
                return allemailaddress;
            }
        }
        #endregion getEmailAddress()

        /// <summary>
        /// This function is used for checking for the username and password for what the user
        /// has entered and return the result
        /// </summary>
        /// <param name="Username"></param>
        /// <param name="Password"></param>
        /// <returns></returns>
        #region Login user Credentials
        public int LoginUser(String Username, String Password)
        {
            using (SqlCommand command = new SqlCommand("user_Logins", conn))
            {
                //Open the SQL Connection
                conn.Open();

                //Pass in the required parameter by the stored procedure.
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@name", SqlDbType.VarChar, 100).Value = Username;
                command.Parameters.Add("@password", SqlDbType.VarChar, 100).Value = Password;

                //Execute the reader for the results
                reader = command.ExecuteReader();

                //If results found from the database
                if (reader.HasRows)
                {
                    return 0;
                }
                else
                {
                    return 1;
                }

            }
        }
        #endregion Login Credentials for Users (Login.aspx)

        /// <summary>
        /// This function is used to return the username on the basis of their email address
        /// </summary>
        /// <param name="emailaddress"></param>
        /// <returns></returns>
        #region Forgotten Username
        public List<string> ForgotUsername(String emailaddress)
        {
            using (SqlCommand command = new SqlCommand("forgot_Username", conn))
            {
                List<string> username = new List<string>();
                try
                {
                    //Open the sql Connection
                    conn.Open();

                    //Pass in the parameter required by the stored procedure
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add("@email", SqlDbType.VarChar, 100).Value = emailaddress;

                    //Execute the reader for the results
                    reader = command.ExecuteReader();

                    //if there are the results from the database
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            string logincredentials = reader.GetString(0);
                            //Get the results to the List<...>
                            if (username.Count > 0)
                            {
                                if (logincredentials != "Not Available") { username.Add(logincredentials); }
                            }
                            else
                            {
                                username.Add(logincredentials);
                            }
                        }
                    }
                    //Close the SqlDataReader
                    reader.Close();
                }
                catch (SqlException)
                { }
                finally
                {
                    //If the connection state is open, close the connection
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                }
                //Return the username for the user
            return username;
            }
        }
        #endregion Forgotten Username

        /// <summary>
        /// This function is used for returning the password of the user on the basis of his 
        /// email address
        /// </summary>
        /// <param name="emailaddress"></param>
        /// <returns></returns>
        #region Forgotten Password (Retrieve the Password from the database)
        public List<string> ForgotPassword(String emailaddress)
        {
            using (SqlCommand command = new SqlCommand("forgot_Password", conn))
            {
                List<string> password = new List<string>();

                try
                {
                    //Open the sql Connection
                    conn.Open();

                    //Pass in the parameter required by the stored procedure
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add("@email", SqlDbType.VarChar, 100).Value = emailaddress;

                    //Execute the reader for the results
                    reader = command.ExecuteReader();

                    //if there are the results from the database
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            string logincredentials = reader.GetString(0);
                            //Get the results to the List<...>
                            if (password.Count > 0)
                            {
                                if (logincredentials != "Not Available") { password.Add(logincredentials); }
                            }
                            else
                            {
                                password.Add(logincredentials);
                            }
                        }
                    }
                    //Close the SqlDataReader
                    reader.Close();
                }
                catch (SqlException)
                { }
                finally
                {
                    //If the connection state is open, close the connection
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                }
                //Return the password for the user
                return password;
            }
        }
        #endregion Forgotten Password

    }
        
}
